/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package br.com.pharmproject.dao;


import br.com.pharmproject.bean.Cliente;
import br.com.pharmproject.bean.Endereco;
import br.com.pharmproject.conexao.Conexao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author rodrigo
 */
public class EnderecoDAO {
    
    Statement stm = null;
    Endereco endereco = null;
    List<Endereco> enderecos = null;
    
    public EnderecoDAO(){
        
         Conexao.getInstance();
         
        
        try {
            stm = Conexao.getInstance().getConector().createStatement();
            
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    
    
    
    public Endereco getById(int idCategoria) throws SQLException, 
            ClassNotFoundException{
        
         endereco = new Endereco();
         
         
         String sql = "SELECT * FROM ENDERECO end WHERE "
                 + "end.ID_ENDERECO = ?";
         
         PreparedStatement pstm = Conexao.getInstance().getConector()
                 .prepareStatement(sql);
         
         ResultSet rs = pstm.executeQuery();
         
         while(rs.next()){
             
             endereco.setIdEndereco(rs.getInt("ID_ENDERECO"));
             endereco.setRua(rs.getString("RUA"));
             endereco.setBairro(rs.getString("BAIRRO"));
             endereco.setNumero(rs.getString("NUMERO"));
             endereco.getCidade().setIdCidade(rs.getInt("ID_CIDADE"));
             
                         
         }
        
                 
         return endereco;
    }
    
     public Endereco insert(Endereco endereco) throws ClassNotFoundException, 
            SQLException{
        
        
        String sqlUpdate = "INSERT INTO ENDERECO "
              + "(RUA, NUMERO, BAIRRO, ID_CIDADE ,CEP)" + " VALUES "
              + "(?, ?, ?, ?, ?)";
         
        PreparedStatement pstm = Conexao.getInstance()
                 .getConector().prepareStatement(sqlUpdate);
        pstm.setString(1, endereco.getRua());
        pstm.setString(2, endereco.getNumero());
        pstm.setString(3, endereco.getBairro());
        pstm.setInt(4, endereco.getCidade().getIdCidade());
        pstm.setString(5, endereco.getCep());
        
        pstm.executeUpdate(sqlUpdate);
        
        return endereco;
    }
    
    public List<Endereco> getAll() throws SQLException{
        
        enderecos = new ArrayList<Endereco>();
                
        String sql = "SELECT * FROM CIDADE";
        ResultSet rs = stm.executeQuery(sql);
        
        
        while(rs.next()){
            
            endereco = new Endereco();
           
            endereco.setIdEndereco(rs.getInt("ID_ENDERECO"));
            endereco.setRua(rs.getString("RUA"));
            endereco.setBairro(rs.getString("BAIRRO"));
            endereco.setNumero(rs.getString("NUMERO"));
            endereco.getCidade().setIdCidade(rs.getInt("ID_CIDADE"));
            
            enderecos.add(endereco);
        }
        
        return enderecos;
        
    }
    
    public void update(Endereco endereco) throws SQLException{
        
        
        String sqlUpdate = "UPDATE ENDERECO set RUA = '" + endereco.getRua()
                + "', NUMERO = '" + endereco.getNumero()
                + "', BAIRRO = '" + endereco.getBairro()
                + "', ID_CIDADE = '" + endereco.getCidade().getIdCidade()
                + "', CEP = '" + endereco.getCep()
                + " WHERE ID_CLIENTE = " + endereco.getIdEndereco();
        
        
        stm.executeUpdate(sqlUpdate);
               
    }
    
    public void delete(int idEndereco){
        
        String sqlDelete = "DELETE FROM ENDERECO WHERE ID_CIDADE = ? ";
        
        PreparedStatement pstm = null;
        ResultSet rs = null;
                
        try {
               
            pstm = Conexao.getInstance().getConector().prepareStatement(sqlDelete);
            pstm.setInt(1, idEndereco);
            
            pstm.executeUpdate();
                       
            
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            Conexao.getInstance().closeConnection();
        }
        
    }
    
}
